package com.bw.dao;

import com.bw.pojo.Goods;
import com.bw.pojo.UserGoods;
import com.bw.util.JDBC;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/***************************
 FileAction:

 CreateTime:2022-08-02 10:29
 Author:Asking-mac
 ****************************/
public class GoodsDao {

    public void insertGoods(Goods goods) throws SQLException {
        String sql = "insert into goods values(null,?,?,?,?,?,?,?)";
        Connection conn = JDBC.getConnection();
        PreparedStatement ps = conn.prepareStatement(sql);

        ps.setObject(1,goods.getGoods_name());
        ps.setObject(2,goods.getGoods_price());
        ps.setObject(3,goods.getGoods_des());
        ps.setObject(4,goods.getGoods_num());
        ps.setObject(5,goods.getUpload_people());
        ps.setObject(6,goods.getGoods_img());
        ps.setObject(7,goods.getUpload_time());

        ps.executeUpdate();
    }


    public List<Goods> selectAll() throws SQLException {
        String sql = "select * from goods";
        Connection conn = JDBC.getConnection();
        PreparedStatement ps = conn.prepareStatement(sql);

        ResultSet rs = ps.executeQuery();

        List<Goods> list = new ArrayList<>();
        while(rs.next()){
            Goods goods = new Goods(rs.getInt(1),
                    rs.getString(2),
                    rs.getDouble(3),
                    rs.getString(4),
                    rs.getInt(5),
                    rs.getString(6),
                    rs.getString(7),
                    rs.getString(8));

            list.add(goods);
        }
        return list;
    }

    public void deleteGoods(String id) throws SQLException {
        String sql = "delete from goods where id = ?";
        Connection conn = JDBC.getConnection();
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setObject(1,id);
        ps.executeUpdate();
    }

    public void updateGoods(Goods goods) throws SQLException {
        String sql = "update goods set goods_name = ?," +
                "goods_price = ?," +
                "goods_des = ?," +
                "goods_num = ?," +
                "upload_people = ?," +
                "goods_img = ?," +
                "upload_time = ? where id = ?";
        Connection conn = JDBC.getConnection();
        PreparedStatement ps = conn.prepareStatement(sql);

        ps.setObject(1,goods.getGoods_name());
        ps.setObject(2,goods.getGoods_price());
        ps.setObject(3,goods.getGoods_des());
        ps.setObject(4,goods.getGoods_num());
        ps.setObject(5,goods.getUpload_people());
        ps.setObject(6,goods.getGoods_img());
        ps.setObject(7,goods.getUpload_time());
        ps.setObject(8,goods.getId());

        ps.executeUpdate();
    }


    public List<UserGoods> selectAllUserGoods() throws SQLException {
        String sql1 = "select username,password from user where id = ?";
        String sql2 = "select goods_name,goods_price from goods where id = ?";

        Connection conn = JDBC.getConnection();
        PreparedStatement ps1 = conn.prepareStatement(sql1);

        ps1.setObject(1,3);
        ResultSet rs1 = ps1.executeQuery();

        PreparedStatement ps2 = conn.prepareStatement(sql2);
        ps2.setObject(1,90);
        ResultSet rs2 = ps2.executeQuery();

        UserGoods userGoods = new UserGoods(rs1.getString(1), rs1.getString(2), rs2.getString(1), rs2.getDouble(2));

        List<UserGoods> list = new ArrayList<>();

        list.add(userGoods);

        return list;

    }


    public List<Goods> selectOrderByPriceDesc() throws SQLException {
        String sql = "select * from goods order by goods_price desc";
        Connection conn = JDBC.getConnection();
        PreparedStatement ps = conn.prepareStatement(sql);

        ResultSet rs = ps.executeQuery();

        List<Goods> list = new ArrayList<>();
        while(rs.next()){
            Goods goods = new Goods(rs.getInt(1),
                    rs.getString(2),
                    rs.getDouble(3),
                    rs.getString(4),
                    rs.getInt(5),
                    rs.getString(6),
                    rs.getString(7),
                    rs.getString(8));

            list.add(goods);
        }
        return list;
    }

    public List<Goods> selectOrderByPriceAsc() throws SQLException {
        String sql = "select * from goods order by goods_price";
        Connection conn = JDBC.getConnection();
        PreparedStatement ps = conn.prepareStatement(sql);

        ResultSet rs = ps.executeQuery();

        List<Goods> list = new ArrayList<>();
        while(rs.next()){
            Goods goods = new Goods(rs.getInt(1),
                    rs.getString(2),
                    rs.getDouble(3),
                    rs.getString(4),
                    rs.getInt(5),
                    rs.getString(6),
                    rs.getString(7),
                    rs.getString(8));

            list.add(goods);
        }
        return list;
    }
}
